package yuan.deng.dao;

import yuan.deng.domain.Student;
import yuan.deng.utils.JDBCUtlis;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {

    //查询所有，用最开始的方法写的
    @Override
    public List<Student> findAll() {
        List<Student> list=new ArrayList<>();
        Connection connection =null;
        Statement statement =null;
        ResultSet resultSet =null;
        //导入jar包
        try {
            //注册驱动,全限定类名反射获取注册驱动对象  5版本后可以省略不做
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接  1获取执行者对象，2管理事务,3释放资源
            connection = DriverManager.getConnection("jdbc:mysql://192.168.96.129:3306/db8", "root", "root");
            //获取执行者对象,执行sql语句
            statement = connection.createStatement();
            //执行sql语句接受结果
            String sql="select * from student";
            resultSet = statement.executeQuery(sql);
            //处理结果
            while (resultSet.next()){//读取并且存入集合
                int id = resultSet.getInt(1);
                String name = resultSet.getString(1);
                int age = resultSet.getInt(1);
                Student stu=new Student(id,name,age);
                list.add(stu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            //释放资源
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(resultSet!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    private Connection connection=null;//连接对象
    private PreparedStatement ps=null;//预定义操作对象
    private ResultSet resultSet=null;//结果集对象
    //增加学生
    @Override
    public int add(Student student) {
        int i=0;
        try {
            connection= JDBCUtlis.getConn();//获取连接
            ps = connection.prepareStatement("insert into student values(?,?,?)");//预定义操作对象
            ps.setInt(1,student.getId());//给？赋值
            ps.setString(2,student.getName());
            ps.setInt(3,student.getAge());
            i = ps.executeUpdate();//返回增加结果
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtlis.close(connection,ps);
        }
        return i;
    }
    //模糊查询
    @Override
    public List<Student> findLike(String s) {
        List<Student> list=new ArrayList<>();
        try {
            connection= JDBCUtlis.getConn();//获取连接
            ps = connection.prepareStatement("select * from student where name like ?");//预定义操作对象
            ps.setString(1,"%"+s+"%");//给？赋值
            resultSet = ps.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                int age = resultSet.getInt(3);
                Student student=new Student(id,name,age);
                list.add(student);//返回查询结果集
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtlis.close(connection,ps,resultSet);
        }
        return list;

    }
    //删除id
    @Override
    public int del(int id) {
        int a=0;
        try {
            connection= JDBCUtlis.getConn();//获取连接
            ps = connection.prepareStatement("delete from student where id = ?");//预定义操作对象
            ps.setInt(1,id);//给？赋值
            a = ps.executeUpdate();//返回删除结果
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtlis.close(connection,ps,resultSet);
        }
        return a;
    }
}
